<?php
/**
 * 《上网日志》实现类
 *
 * @author 孙晓晔
 * @version $Id: Network.class.php 1035 2010-03-26 05:24:59Z sunxy $
 */

require_once(dirname(__FILE__) . '/../AdminUtil.class.php');

class Layer7 extends AdminUtil {
	public $tbl = Constant::tbl_layer7;

	public $proto = array(
        '-' => '请选择',
        'TCP' => 'TCP',
        'UDP' => 'UDP',
	);
	
	public $ip = array(
        '-' => '请选择',
        '192.168.2.' => '南山',
        '192.168.3.' => '沙井',
	);
	
	public $type = array(
        '-' => '请选择',
       'qq' => 'qq',
       'msn' => 'msn',
		'skypeout' => 'skypeout',
		'skypetoskype' => 'skypetoskype',
		'bittorrent' => 'bittorrent',
		'kugoo' => 'kugoo',		
		'pplive' => 'pplive',
		'live365' => 'live365',		
		'xunlei' => 'xunlei',	
	);

	public $orderby = array(
        '-' => '请选择',
		'src' => '源地址',
		'dst' => '目地址',
		'spt' => '源端口 ',
		'dpt' => '目端口',
		'1' => '创建时间',
		'2' => '更新时间',
	);

	public $groupby = array(
		'-' => '请选择',
		'type' => '类型',
		'proto' => '协议',
		'src' => '源地址',
		'dst' => '目地址',
		'spt' => '源端口',
		'dpt' => '目端口',		
		'2' => '日期',
		'3' => '月份',
	);

	function __construct() {
		$request['update'] = '$Date: 2009/03/06 11:29:07 $';
		$request['revision'] = '$Revision: 1.2 $';

		parent::__construct($request);
	}

	function pretreat($controller) {
		parent::pretreat($controller);

		$controller->assign_by_ref('proto_option', $this->proto);
		$controller->assign_by_ref('ip_option', $this->ip);
		$controller->assign_by_ref('type_option', $this->type);
	}

	/**
	 * 首页
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function layout($controller, $request) {

		$controller->display($request, 'log/action');
		return true;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function find($controller, $request) {
		$this->pretreat($controller);
			
		// $controller->assign('t1', strtotime('-1 day'));
		// $controller->assign('t2', strtotime('+1 day'));

		$controller->display($request, 'log/layer7/find');
		return true;
	}

	function _orderby_x($controller, $row, $px) {
		$orderby = $this->orderby['1'];
		$row['orderby_x'] = $row['createtime'];

		if (empty($px) || $px == '-' || $px == '1') {
			$controller->assign('orderby', $orderby);

			return $row;
		}

		if ($px == '2') {
			$orderby = $this->orderby[$px];
			$row['orderby_x'] = $row['modifytime'];
		}

		if ($px == '`join`') {
			$orderby = $this->orderby[$px];
			$row['orderby_x'] = $row['join'];
		}

		$controller->assign('orderby', $orderby);

		return $row;
	}

	function _tbl($t1) {
		if (empty($t1)) {
			return $this->tbl;
		}

		$dd = date("Ymd", strtotime($t1));
		return sprintf("%s_%s", $this->tbl, $dd);
	}
	
	function _where($request) {
		$src_s = $request['src_s'];
		$dst_s = $request['dst_s'];
		
		$proto_s = $request['proto_s'];
		$type_s = $request['type_s'];
		
		$spt_s = $request['spt_s'];
		$dpt_s = $request['dpt_s'];

		$where = " WHERE 1=1";
		if (!empty($src_s)) {
			$where .= " AND src LIKE '" . $src_s . "%'";
		}
		
		if (!empty($dst_s)) {
			$where .= " AND dst LIKE '" . $dst_s . "%'";
		}

		if (!empty($proto_s) && $proto_s <> '-') {
			$where .= " AND proto = '" . $proto_s . "'";
		}
		
		if (!empty($type_s) && $type_s <> '-') {
			$where .= " AND type = '" . $type_s . "'";
		}		

		if (!empty($spt_s)) {
			$where .= " AND spt = " . $spt_s;
		}

		if (!empty($dpt_s)) {
			$where .= " AND dpt = " . $dpt_s;
		}

		$where .= $this->_where_default($request);
		$where = $this->_where_aa($where);
		return $where;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function findPost($controller, $request) {
		$this->pretreat($controller);

		$this->findPara($controller, $request);

		$db = newdb($this->dsn_r);
		$fmt = "SELECT * FROM %s a %s %s";
		$where = $this->_where($request);
		$orderby = $this->_orderby();
		$sql = sprintf($fmt, $this->tbl, $where, $orderby);
		debug($sql);

		$pager_option = array (
            'db' => $db,
            'sql' => $sql,
            'PageSize' => $this->pagesize,
            'CurrentPageID' => $request['p'],
            'numItems' => $request['n']
		);

		$pager = @new Pager($pager_option);
		$data = $pager->getPageData();

		if ($pager->number > 0) {
			$count = 0;
			$from = $pager->from;
			while ($count < $pager->number) {
				$row = $data[$count];

				// $row['proto_t'] = $this->proto[$row['proto']];

				$row['emp'] = $this->_emp_row($row);

				if (!empty($row['emp'])) {
					$row['emp_t'] = Name::emp($row['emp']);
				}

				$row = $this->_orderby_x($controller, $row, $this->orderby_s);

				$data[$count] = $row;
				$count++;
			}
			$controller->assign_by_ref('result', $data);
			$controller->assign_by_ref('pager', $pager);
		}

		$controller->display($request, 'log/layer7/list');
		return true;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function group($controller, $request) {
		$this->pretreat($controller);

		// $controller->assign('t1', strtotime('-7 day'));
		// $controller->assign('t2', strtotime('+1 day'));

		$controller->display($request, 'log/layer7/group');
		return true;
	}

	function groupby($controller, $px) {
		$px = $this->groupby_aa($px, $this->groupby, 'src');

		$controller->assign('groupby', $this->groupby[$px]);

		return $this->groupby_dd($px);
	}
	
	function _name($row, $px) {
		if (empty($px) || $px == '-' || $px == 'src') {
			$row['a'] = $this->_emp_ip($row['a']);
		} elseif ($px == 'src' || $px == 'dst') {
			$row['a'] = $this->_emp_ip($row['a']);
		} else {
		
			$row = $this->_name_t($row, $px);
		}

		return $row;
	}

	function aa($row) {
		$dd = date('Y-m-d');
		$db = newdb($this->dsn);
		$fmt = "SELECT * FROM %s WHERE dd = '%s' and fx = '%s' and ip = '%s'";
		$sql = sprintf($fmt, Constant::tbl_network_flow_daily, $dd, $row['fx'], $row['ip']);
		// echo $sql . "\n";
		$result = $db->query($sql);
		if ($db->num_rows($result) > 0) {
			$fmt = "UPDATE %s SET pkts = pkts + %s, bytes = bytes + %s where dd = '%s' and fx = '%s' and ip = '%s'";
		} else {
			$fmt = "INSERT INTO %s(pkts, bytes, dd, fx, ip, createtime) VALUES('%s', '%s', '%s', '%s', '%s', now())";
		}
		$sql = sprintf($fmt, Constant::tbl_network_flow_daily, $row['pkts'], $row['bytes'], $dd, $row['fx'], $row['ip']);
		// echo $sql . "\n";
		$db->query($sql);
	}

	function z($controller, $request) {
		$this->pretreat($controller);

		$db = newdb($this->dsn);
		$fmt = "SELECT * FROM %s";
		$sql = sprintf($fmt, $this->tbl);
		echo $sql . "\n";
		$result = $db->query($sql);
		while ($row = $db->fetch_array($result)) {
			$this->aa($row);
			echo ".";
		}

		$fmt = "truncate table %s";
		$sql = sprintf($fmt, $this->tbl);
		// echo $sql . "\n";
		$db->query($sql);

		$cmd = "/sbin/iptables -L -n -vx";
		$ret = shell_exec($cmd);

		// echo $ret;

		$aa = preg_split("/[\\n]+/i", $ret);
		// print_r($a);

		$rr = array();
		$fx = '1';

		while (list($k, $v) = each($aa)) {
			$buf = trim($v);
			if (strlen($buf) < 10) {
				continue;
			}
			// echo $buf . "\n";

			if (preg_match("/input/i", $buf)) {
				$fx = '2';
				echo $buf . "\n";
				continue;
			}

			if (preg_match("/output/i", $buf)) {
				$fx = '1';
				echo $buf . "\n";
				continue;
			}

			if (preg_match("/^(^Chain|pkts)/i", $buf)) {
				echo $buf . "\n";
				continue;
			}

			$a = preg_split("/[\t|\s]+/i", $buf);
			// print_r($a);
			if ($a[0] == 0 || count($a) <> 9) {
				continue;
			}

			echo $fx . "\n";
			echo $buf . "\n";
			// print_r($a);

			if ($fx == '1' && $a[7] == '0.0.0.0/0') {
				continue;
			}

			if ($fx == '2' && $a[8] == '0.0.0.0/0') {
				continue;
			}

			if ($fx == '1') {
				$ip = $a[7];
			} else {
				$ip = $a[8];
			}

			$rr[] = $a;

			$pkts = $a[0];
			$bytes = $a[1];

			$fmt = "INSERT INTO %s(fx, pkts, bytes, ip, createtime) VALUES('%s', '%s', '%s', '%s', now())";
			$sql = sprintf($fmt, $this->tbl, $fx, $pkts, $bytes, $ip);
			echo $sql . "\n";
			$db->query($sql);

			echo ".";
		}

		$cmd = "/sbin/iptables -Z";
		$ret = shell_exec($cmd);

		logdate($controller, $request);
		return true;
	}

	function mac_content($ip) {
		if (empty($ip)) {
			return '';
		}

		if ($row = Common::x(Constant::tbl_emp_mac, 'ip', $ip)) {
			return $row['content'];
		}

		return "";
	}

	function _emp_row($row) {
		if (preg_match("/^192.168.(2|3)/i", $row['src'])) {
			$ip = $row['src'];
		}

		if (preg_match("/^192.168.(2|3)/i", $row['dst'])) {
			$ip = $row['dst'];
		}

		if (empty($ip)) {
			return '';
		}

		if ($row = Common::x(Constant::tbl_emp_mac, 'ip', $ip)) {
			return $row['emp'];
		}

		return "";
	}

	function _emp_ip($ip) {
		if (empty($ip)) {
			return '';
		}

		if (!preg_match("/^192.168.(2|3)/i", $ip)) {
			return $ip;
		}

		if ($row = Common::x(Constant::tbl_emp_mac, 'ip', $ip)) {
			return sprintf("%s(%s)", Name::emp($row['emp']), $ip);
		}

		return $ip;
	}

	function a($controller, $request) {

		// $s = "Jun 12 11:11:31";
		// echo date("Y-m-d H:i:s", strtotime($s));

		$s = "SRC=121.15.207.29";
		// echo $this->_value($s);

		$a = array("Jun", "12", "11:11:31");
		// echo $this->_createtime($a);

		echo $this->_emp_ip("192.168.2.30");

		echo "\n" . $_REQUEST['do'] . "\n";

		logdate($controller, $request);
		return true;
	}

	function _value($s) {
		$b = preg_split("/=/i", $s);

		return $b[1];
	}

	function _createtime($a) {
		$s = sprintf("%s %s %s", $a[0], $a[1], $a[2]);
		// echo $s;

		return date("Y-m-d H:i:s", strtotime($s));
	}

	function _proto($s) {
		$proto = $this->_value($s);

		if ($proto == 'TCP') {
			return 1;
		}

		return 2;
	}

	function b($controller, $request) {
		$db = newdb($this->dsn);

		echo "\n" . $_REQUEST['do'] . "\n";

		logdate($controller, $request);
		return true;
	}

	function backup($controller, $request) {
		$db = newdb($this->dsn);
		$dd = date("Ymd");
		$tbl = sprintf("%s_%s", $this->tbl, $dd);

		$fmt = "SELECT COUNT(*) FROM %s";
		$sql = sprintf($fmt, $tbl);
		$result = $db->query($sql);
		$row = $db->fetch_array($result);
		if ($row[1] > 1000000) {
			$fmt = "TRUNCATE TABLE %s";
			$sql = sprintf($fmt, $this->tbl);
			// debug($sql);
			$db->query($sql);
			return false;
		}

		$sql = sprintf("DESCRIBE %s", $tbl);
		$db->query($sql);
		if ($db->errno() == '1146') {
			$fmt = "CREATE TABLE  %s (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `src` varchar(20) DEFAULT NULL,
  `dst` varchar(20) DEFAULT NULL,
  `len` smallint(6) DEFAULT NULL,
  `proto` tinyint(4) DEFAULT NULL,
  `spt` int(11) DEFAULT NULL,
  `dpt` int(11) DEFAULT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `src` (`src`),
  KEY `dst` (`dst`),
  KEY `proto` (`proto`),
  KEY `dpt` (`dpt`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8";
			$sql = sprintf($fmt, $tbl);
			$db->query($sql);
		}
		echo "dst table = " . $tbl . "\n\r";

		$dst = "src, dst, len, proto, spt, dpt, createtime";
		$src = "src, dst, sum(len), proto, spt, dpt, createtime";

		$fmt = "INSERT INTO %s(%s) SELECT %s FROM %s GROUP BY src, spt";
		$sql = sprintf($fmt, $tbl, $dst, $src, $this->tbl);
		// echo $sql . "\n";
		// debug($sql);
		$db->query($sql);

		$fmt = "TRUNCATE TABLE %s";
		$sql = sprintf($fmt, $this->tbl);
		// debug($sql);
		$db->query($sql);
	}

	function t($controller, $request) {
		$db = newdb($this->dsn);
				
		$fmt = "INSERT INTO %s(type, src, dst, len, proto, spt, dpt, createtime) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";

		// $file = "/home/sunxy/messages";
		$file = "/var/log/iptables.log";
		$fd = fopen($file, "r");
		$count = 0;
		while (!feof($fd)) {
			$buf = trim(fgets($fd));
			// echo $buf . "\n";
			if (strlen($buf) < 20) {
				continue;
			}			

			if (!preg_match("/(kernel: )(.*)(IN=)(.*)( SRC=)([\d|\\.]+)( DST=)([\d|\\.]+)( LEN=)(\d+)(.*)( PROTO=)(.*)( SPT=)(\d+)( DPT=)(\d+)/", $buf, $m)) {
				continue;
			}
			
			$a = preg_split("/[\\s,]+/", $buf);
			// print_r($a);

			$createtime = $this->_createtime($a);
			
			print_r($m);
			$type = $m[2];
			$src = $m[6];
			$dst = $m[8];
			$len = $m[10];
			$proto = $m[13];
			$spt = $m[15];
			$dpt = $m[17];			

			$sql = sprintf($fmt, $this->tbl, $type, $src, $dst, $len, $proto, $spt, $dpt, $createtime);
			echo $sql . "\n";
			$db->query($sql);
			if ($db->affected_rows() > 0) {
				echo ".";
			} else {
				echo "E";
			}
		}
		fclose($fd);

		$fmt = "echo '' > %s\n";
		$cmd = sprintf($fmt, $file);
		// echo $cmd;
		shell_exec($cmd);
		
		// $this->_backup();

		echo "\n" . $_REQUEST['do'] . "\n";

		logdate($controller, $request);
		return true;
	}
	
	function _backup() {
		$dd = date("Ymd");
		$tbl = sprintf("%s_%s", $this->tbl, $dd);
		
		$db = newdb($this->dsn);
		$fmt = "SELECT COUNT(*) FROM %s";
		$sql = sprintf($fmt, $tbl);
		$result = $db->query($sql);
		$row = $db->fetch_array($result);
		if ($row[1] > 1000000) {
			$fmt = "TRUNCATE TABLE %s";
			$sql = sprintf($fmt, $this->tbl);
			// debug($sql);
			$db->query($sql);
			return false;
		}

		$sql = sprintf("DESCRIBE %s", $tbl);
		$db->query($sql);
		if ($db->errno() == '1146') {
			$fmt = "CREATE TABLE  %s (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `src` varchar(20) DEFAULT NULL,
  `dst` varchar(20) DEFAULT NULL,
  `len` smallint(6) DEFAULT NULL,
  `proto` tinyint(4) DEFAULT NULL,
  `spt` int(11) DEFAULT NULL,
  `dpt` int(11) DEFAULT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `src` (`src`),
  KEY `dst` (`dst`),
  KEY `proto` (`proto`),
  KEY `dpt` (`dpt`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8";
			$sql = sprintf($fmt, $tbl);
			$db->query($sql);
		}
		echo "dst table = " . $tbl . "\n\r";

		$dst = "src, dst, len, proto, spt, dpt, createtime";
		$src = "src, dst, sum(len), proto, spt, dpt, createtime";

		$fmt = "INSERT INTO %s(%s) SELECT %s FROM %s GROUP BY src, spt";
		$sql = sprintf($fmt, $tbl, $dst, $src, $this->tbl);
		// echo $sql . "\n";
		// debug($sql);
		$db->query($sql);		
	}
	
	function qq($controller, $request) {
		$db = newdb('ns');
		$fmt = "SELECT dst AS a, COUNT(*) AS c FROM %s a WHERE src LIKE '%s' AND type = 'qq' GROUP BY dst ORDER BY c DESC";
		$sql = sprintf($fmt, $this->tbl, "192.168.%");
		echo $sql . "\n";
		$result = $db->query($sql);
		while ($row = $db->fetch_array($result)) {
			if ($row['c'] < 5) {
				break;
			}
			
			$this->_qq($row['a']);
		}
		
		$fmt = "TRUNCATE TABLE %s";
		$sql = sprintf($fmt, $this->tbl);
		echo $sql . "\n";
		$db->query($sql);
		
		echo "\n" . $_REQUEST['do'] . "\n";

		logdate($controller, $request);
		return true;
	}
	
	function _qq($ip) {
		$db = newdb('_fy');
		$fmt = "INSERT INTO %s(ip, netmask, category, content, status, createtime, modifytime)";
		$fmt .= " VALUES('%s', '255.255.255.0', 'qq', '', '2', now(), now())";
		$sql = sprintf($fmt, Constant::tbl_ip_qq, $ip);
		echo $sql . "\n";
		$db->query($sql);
	}
}
?>
